package com.poi.demopoi.controller2;

import com.google.common.collect.Lists;
import com.poi.demopoi.common.BaseController;
import com.poi.demopoi.service.IEmployeeService;
import com.poi.demopoi.service.impl.MesService;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

//能C
@RestController
@RequestMapping("/mes")
public class CCCMesController extends BaseController {
    private static final Logger log = LoggerFactory.getLogger(CCCMesController.class);

    @Autowired
    private MesService mesService;
    @Autowired
    private IEmployeeService employeeService;





    //——————————————————————————————————————————————————————
    //——————————————————————————————————————————————————————
    //——————————————————————————————————————————————————————
    //——————————————————————————————————————————————————————
    //——————————————————————————————————————————————————————
    // https://www.jianshu.com/p/9fd84f1ce725


    /** POI导入Excel ： https://www.jianshu.com/p/33f86f41b948
     * //poi4.10 Java8让Excel的 读写 变得更加简单高效  https://github.com/liuhuagui/gridexcel
     *
     * @param response  , Long companyId
     */
//    @GetMapping(value = "/importExcel11")
//    public void importExcel(MultipartFile file, HttpServletResponse response)  {
//        try {
////        String filePath = FiledExcelPath + UpLoad.upLoadFile(file, FiledExcelPath);
//
////        InputStream fs = new ClassPathResource("/export_templates/stockExportAmount21.xlsx").getInputStream();
////            String filePath = ResourceUtils.getFile("classpath:export_templates/111导入测试2.xlsx").getPath();
//            String filePath = ResourceUtils.getFile("classpath:export_templates/2007.xlsx").getPath();
//
////        Employee employee = new Employee();
////        Long companyId = employee.getId();
////            List<Employee> employeeList =
////                    ExcelUtils.toList(new File(filePath).getCanonicalPath(),
////                            Employee.class,
////                            new ExcelUtils.ExcelFieldMap[]{
////                                    new ExcelUtils.ExcelFieldMap(0, "id"),
////                                    new ExcelUtils.ExcelFieldMap(1, "name"),
////                                    new ExcelUtils.ExcelFieldMap(2, "age"),
////                                    new ExcelUtils.ExcelFieldMap(3, "email"),
////                            }, 0);
//
////            FileInputStream fileInputStream = new FileInputStream(new File(filePath).getCanonicalPath());
////            XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
////            XSSFSheet sheet = wb.getSheetAt(0);
////            String[] ss = new String[]{"id","name","age","email"};
////            List<Employee> employeeList2 = PoiU3.readSheet(sheet, new Employee(null,null,null,null),  ss);
//////            List<Employee> employeeList2 = PoiU3.readSheet(sheet, new Employee());
//
//
////            InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("export_templates/2007.xlsx");
//            InputStream resourceAsStream = new FileInputStream(new File(filePath).getCanonicalPath());
//            ArrayList<Employee> employeeList2 = new ArrayList<>();
//            XLSXEventModel xlsx = new XLSXEventModel(resourceAsStream,
//                    1,
//                    cs -> {
//                        Employee tradeOrder = new Employee();
//                        tradeOrder.setId(Long.valueOf(cs.get(0)));
//                        //  tradeOrder.setName(cs.get(16));
//                        //  tradeOrder.setAge(Integer.valueOf(cs.get(16)));
//                        //  tradeOrder.setEmail(cs.get(16));
//                        employeeList2.add(tradeOrder);
//                    });
//            xlsx.process();
//
//
//            //插入数据
////        Util.sublist(configurations, l -> EmployeeMapper.insertAll(l, companyId));
//            Util.sublist(employeeList2, l -> employeeService.saveBatch(l));
//        } catch (IOException e) {
//            e.printStackTrace();
//        } catch (OpenXML4JException e) {
//            e.printStackTrace();
//        }catch (SAXException e) {
//            e.printStackTrace();
//        } catch (ParserConfigurationException e) {
//            e.printStackTrace();
//        }
////        catch (InvocationTargetException e) {
////            e.printStackTrace();
////        } catch (InstantiationException e) {
////            e.printStackTrace();
////        } catch (IllegalAccessException e) {
////            e.printStackTrace();
////        }
//
//
//    }


    /**
     * 导出Excel所有
     *
     * @param response  , Long companyId
     */
    @GetMapping(value = "/export11")
    public void exportExcel(HttpServletResponse response) {
//        List<EpcSystemDictionary> epcSystemDictionaryList = epcSystemDictionaryMapper.getAll(companyId);
        List<MesExceptionBillzDTO> epcSystemDictionaryList =
                Lists.newArrayList(
                        new MesExceptionBillzDTO("1", "1", "电装1", "DZ-1", "异常1"),
                        new MesExceptionBillzDTO("2", "2", "电装2", "DZ-2", "异常2"),
                        new MesExceptionBillzDTO("3", "3", "电装3", "DZ-3", "异常3"),
                        new MesExceptionBillzDTO("4", "4", "电装4", "DZ-4", "异常4")
                );

        ExcelExportOut excelExportOut = new ExcelExportOut(response, "数据字典总数据", "sheet1");
        excelExportOut.writeBigExcel(response, "数据字典总数据",
                new String[]{"id", "typeName", "typeNameL", "exTittle", "exDescribeInfo"}
                , new String[]{"编号", "名称", "英文名称", "层级", "排序"}
                , new int[]{30, 30, 30, 30, 30}, epcSystemDictionaryList);

//        ExcelExport excelExport = new ExcelExport(response, "数据字典总数据", "sheet1");
//        excelExport.writeExcel(
//                new String[]{"id", "typeName", "typeNameL", "exTittle", "exDescribeInfo"}
//                , new String[]{"编号", "名称", "英文名称", "层级", "排序"}
//                , new int[]{30, 30, 30, 30, 30}, epcSystemDictionaryList);
    }

//    /**
//     * 保有量-导出Excel
//     *
//     * @param response
//     */
//    @Override
//    public void exportInventoryResult(HttpServletResponse response, Map map, Long companyId) {
//        List<InventoryDto> inventoryDtoList = inventoryMapper.selectExportInventoryResult(map, companyId);
//        try {
//            ExcelExport excelExport = new ExcelExport();
//            excelExport.writeBigExcel(response, "保有量导出数据", new String[]{"materialCode", "materialName", "number", "countryArea"}
//                    , new String[]{"物料号", "物料名称", "数量", "国家大区"}
//                    , new int[]{30, 30, 30, 30}, inventoryDtoList);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
    //——————————————————————————————————————————————————————

    //★★★Java导出Excel使用POI之SXSSFWorkbook方式   https://www.cnblogs.com/1012hq/p/11496410.html
    @GetMapping(value = "/export02")
    public void export02(HttpServletResponse response, HttpServletRequest request) {
        List<MesExceptionBillzDTO> resultSend =
        Lists.newArrayList(
            new MesExceptionBillzDTO("1", "1", "电装1", "DZ-1", "异常1", "回复1", "回复人1", "回复时间1"),
            new MesExceptionBillzDTO("1", "1", "电装1", "DZ-1", "异常1", "回复11", "回复人11", "回复时间11"),
            new MesExceptionBillzDTO("1", "1", "电装1", "DZ-1", "异常1", "回复12", "回复人12", "回复时间12"),
            new MesExceptionBillzDTO("2", "2", "电装2", "DZ-2", "异常2", "回复2", "回复人2", "回复时间2"),
            new MesExceptionBillzDTO("3", "3", "电装3", "DZ-3", "异常3", "回复3", "回复人3", "回复时间3"),
            new MesExceptionBillzDTO("3", "3", "电装3", "DZ-3", "异常3", "回复31", "回复人31", "回复时间31"),
            new MesExceptionBillzDTO("4", "4", "电装4", "DZ-4", "异常4", "回复4", "回复人4", "回复时间4")
        );

        //实际做法：先找个xlsx文件，画上模拟的数据，然后在通过代码调试进行创建导出
        try {
//            mesService.exportData(1, null, null, resultSend);

            int sheetNum = 0;

//            String filePath = "E:\\000temp\\test4.xlsx";
//            File file = new File(filePath);
//            XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(file));
//            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);

            XSSFWorkbook workbook1 = new XSSFWorkbook();
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);


            // 生成一个表格
            Sheet sheet = sxssfWorkbook.createSheet();
//            Sheet sheet = sxssfWorkbook.getSheetAt(0);

            /* *********** ***********第一行标题start*************************** */
            // 产生表格标题行 单元格范围 参数（int firstRow, int lastRow, int firstCol, intlastCol)
            //这里设置标题单元格的合并范围，0,0,4,6代表第0行的4-6列合并为一列，0,1,0,0代表第0列的第0行到第1行合并为一行
            CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6);//第0行的第4-6列合并
            CellRangeAddress region2 = new CellRangeAddress(0, 1, 7, 7);//第1行的第7列合并
            CellRangeAddress region3 = new CellRangeAddress(0, 1, 8, 8);
            CellRangeAddress region4 = new CellRangeAddress(0, 1, 9, 9);
            CellRangeAddress region5 = new CellRangeAddress(0, 1, 10, 10);
            CellRangeAddress region6 = new CellRangeAddress(0, 1, 11, 11);
            CellRangeAddress region7 = new CellRangeAddress(0, 1, 12, 12);
            CellRangeAddress region8 = new CellRangeAddress(0, 1, 0, 0);//第0行到第1行合并为一行
            CellRangeAddress region9 = new CellRangeAddress(0, 1, 1, 1);//第0行到第2行合并为一行
            CellRangeAddress region10 = new CellRangeAddress(0, 1, 2, 2);//第0行到第3行合并为一行
            CellRangeAddress region11 = new CellRangeAddress(0, 1, 3, 3);//第0行到第4行合并为一行
//            CellRangeAddress region11 = new CellRangeAddress(2, 2, 0, 0);//第3行到第1列合并为一行

            // 在sheet里将表头单元格合并
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region4);
            sheet.addMergedRegion(region5);
            sheet.addMergedRegion(region6);
            sheet.addMergedRegion(region7);
            sheet.addMergedRegion(region8);
            sheet.addMergedRegion(region9);
            sheet.addMergedRegion(region10);
            sheet.addMergedRegion(region11);

            // 设置sheet名字
            sxssfWorkbook.setSheetName(sheetNum, "异常问题导出");
            /* **********************第二行标题start*************************** */
            // 表格第二行标题
            Row headTitleSecond = sheet.createRow(0);
            // 设置样式
            CellStyle secondRowcellStyle = sxssfWorkbook.createCellStyle();
            secondRowcellStyle.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色
            //
//            secondRowcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
//            secondRowcellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//            secondRowcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            secondRowcellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//            secondRowcellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            Font font = sxssfWorkbook.createFont();
            font.setFontName("宋体");
            font.setFontHeight((short) 220);
//            font.setBoldweight((short) 700);
            secondRowcellStyle.setFont(font);
            Cell cel = headTitleSecond.createCell(0);
            cel.setCellStyle(secondRowcellStyle);
            cel.setCellValue("异常类型");
            // 设置样式
            Cell cel1 = headTitleSecond.createCell(1);
            cel1.setCellStyle(secondRowcellStyle);
            cel1.setCellValue("产品项目");
            Cell ce2 = headTitleSecond.createCell(2);
            ce2.setCellStyle(secondRowcellStyle);
            ce2.setCellValue("异常标题");
            Cell cel3 = headTitleSecond.createCell(3);
            cel3.setCellStyle(secondRowcellStyle);
            cel3.setCellValue("异常内容");
            Cell cel4 = headTitleSecond.createCell(4);
            cel4.setCellStyle(secondRowcellStyle);
            cel4.setCellValue("异常回复");
            Cell cel5 = headTitleSecond.createCell(7);
            cel5.setCellStyle(secondRowcellStyle);
            cel5.setCellValue("是否归零");
            Cell cel6 = headTitleSecond.createCell(8);
            cel6.setCellStyle(secondRowcellStyle);
            cel6.setCellValue("发布人");
            Cell cel7 = headTitleSecond.createCell(9);
            cel7.setCellStyle(secondRowcellStyle);
            cel7.setCellValue("发布时间");
            Cell cel8 = headTitleSecond.createCell(10);
            cel8.setCellStyle(secondRowcellStyle);
            cel8.setCellValue("负责人");
            Cell cel9 = headTitleSecond.createCell(11);
            cel9.setCellStyle(secondRowcellStyle);
            cel9.setCellValue("设计人员");
            Cell cel10 = headTitleSecond.createCell(12);
            cel10.setCellStyle(secondRowcellStyle);
            cel10.setCellValue("是否有附件");

            // 表格第二行标题
            Row headTitle = sheet.createRow(1);
            Cell cel116 = headTitle.createCell(4);
            cel116.setCellStyle(secondRowcellStyle);
            cel116.setCellValue("回复内容");
            Cell cel117 = headTitle.createCell(5);
            cel117.setCellStyle(secondRowcellStyle);
            cel117.setCellValue("回复人");
            Cell cel118 = headTitle.createCell(6);
            cel118.setCellStyle(secondRowcellStyle);
            cel118.setCellValue("回复时间");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Row row = null;
            String curid = "";
            String lastid = "";

            // 遍历集合数据，产生数据行
            if (resultSend != null) {
                int index = 2;
                int begin = 2;
                int end = 2;
                for (int i = 0; i < resultSend.size(); i++) {
                    row = sheet.createRow(index);
                    MesExceptionBillzDTO dto = resultSend.get(i);
                    curid = dto.getId();
                    if (i != 0) {
                        lastid = resultSend.get(i - 1).getId();
                    } else {
                        lastid = curid;
                    }
                    row.setHeightInPoints(22.0f);

                    row.createCell(0).setCellValue((dto.getTypeName() != null) ? dto.getTypeName() : "");
                    row.createCell(1).setCellValue((dto.getTypeNameL() != null) ? dto.getTypeNameL() : "");
                    row.createCell(2).setCellValue((dto.getExTittle() != null) ? dto.getExTittle() : "");
                    row.createCell(3).setCellValue((dto.getExDescribeInfo() != null) ? dto.getExDescribeInfo() : "");
                    row.createCell(4).setCellValue((dto.getExReplyInfo() != null) ? dto.getExReplyInfo() : "");
                    row.createCell(5).setCellValue((dto.getReplierName() != null) ? dto.getReplierName() : "");
                    row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? dto.getLastUpdateDate() : "");
//                    row.createCell(6).setCellValue((dto.getLastUpdateDate() != null) ? sdf.format(dto.getLastUpdateDate()) : "");

//                    row.createCell(7).setCellValue((dto.getStatus() != null) ? (dto.getStatus().equals("N") ? "未归零" : "已归零") : "");
//                    row.createCell(8).setCellValue(sysUserAPI.getSysUserNameById((dto.getCreatedBy() != null) ? dto.getCreatedBy() : ""));
//                    row.createCell(9).setCellValue(sdf.format((dto.getCreationDate() != null) ? dto.getCreationDate() : ""));
//                    row.createCell(10).setCellValue((dto.getDutyManNames() != null) ? dto.getDutyManNames() : "");
//                    row.createCell(11).setCellValue(sysUserAPI.getSysUserNameById((dto.getDesignerNames() != null) ? dto.getDesignerNames() : ""));
//                    row.createCell(12).setCellValue(dto.getCountAttachment() > 0 ? "是" : "否");
                    if (lastid.equals(curid)) {
                        if (i != 0) {
                            end += 1;
                        }
                    } else {
                        for (int a = 0; a < 13; a++) {
                            if (a < 4 || a > 6) {
                                // 合并  new CellRangeAddress(2, 2, 0, 0);//第3行到第1列合并为一行
                                //合并单元格时 报 java.lang.IllegalArgumentException: Merged region A1 must contain 2 or more cells？
                                //解决方法（https://blog.csdn.net/huxiaochao_6053/article/details/87623168）：
                                //合并单元格需注意假如某个单元格不需要合并则就不需要往List<Integer>集合中塞，列如诸如以下数据[2, 2, 1, 1]就是错误数据，
                                // 因为它代表一个单元格B3。B3并不需要合并，而假如执意要合并的话则该sheet.addMergedRegion(new CellRangeAddress(2, 2,1, 1));方法会报错。则将合并 2,2,1,1 去掉就不报错了。
                                if (begin != end) {
                                    CellRangeAddress callRange = new CellRangeAddress(begin, end, a, a);
                                    sheet.addMergedRegion(callRange);
                                }
                            }
                        }
                        end += 1;
                        begin = end;
                    }
                    if (i % 100 == 0) {
                        ((SXSSFSheet) sheet).flushRows();
                    }
                    index++;
                }
                for (int i = 0; i < 13; i++) {
                    if (i < 4 || i > 6) {
                        // 合并
                        if (begin != end) {
                            CellRangeAddress callRange1 = new CellRangeAddress(begin, end, i, i);
                            sheet.addMergedRegion(callRange1);
                        }
                    }
                }
            }

            String filename = String.format("异常问题导出", "");

            String fileNameEn = filename + "_" + UUID.randomUUID().toString() +  ".xlsx";
            String fileNameEn2 = "ruoyi/uploadPath" + "/download/" + fileNameEn;
            File desc = new File(fileNameEn2);
            if (!desc.getParentFile().exists()) {
                desc.getParentFile().mkdirs();
            }

            OutputStream outputWriter = new FileOutputStream(fileNameEn2);
//            ServletOutputStream outputWriter = response.getOutputStream();

            response.setCharacterEncoding("UTF-8");
//            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileNameEn2, "UTF-8"));


            sxssfWorkbook.write(outputWriter);


            String sheetName0 = filename.replace("xlsx", "");
            sxssfWorkbook.setSheetName(0, sheetName0);
            String header = request.getHeader("User-Agent").toUpperCase();
            if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+", "%20");
            }
            response.setContentType(String.format("%s;charset=utf-8", "application/x"));
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    new String(filename.getBytes("utf-8"), "iso8859-1"));

            outputWriter.flush();
            sxssfWorkbook.dispose();
            outputWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping(value = "/export01")
    public void export01()  {
        List<MesExceptionBillzDTO> resultSend =
        Lists.newArrayList(
                new MesExceptionBillzDTO("1", "1", "电装1", "DZ-1", "异常1"),
                new MesExceptionBillzDTO("2", "2", "电装2", "DZ-2", "异常2")
        );

        try {
            mesService.exportDataNoReply(1, null, null, resultSend);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



}
